package com.cindata

import com.cindata.dimension.RiskDimension
import com.cindata.dimension.YearLimitDimension
import grails.converters.JSON
import grails.transaction.Transactional
import org.springframework.security.access.annotation.Secured

@Secured(['isAuthenticated()'])
@Transactional
class ValueAnalyzeController
{

    def dataSource
    def springSecurityService

    static allowedMethods = [save: "POST", update: "PUT", delete: "DELETE"]

    def index()
    {
    }

    /**
     * 公用条件查询*/
    def pubSql(params)
    {
        //城市
        def city = params['city']
        //行政区
        def district = params['district']
        //贷款余额上限
        def loansBalanceStart = params["loansBalanceStart"]
        //贷款余额下限
        def loansBalanceEnd = params["loansBalanceEnd"]
        //贷款金额上限
        def loansAmountStart = params["loansAmountStart"]
        //贷款金额下限
        def loansAmountEnd = params["loansAmountEnd"]
        //放款时间开始
        def startDateStart = params["startDateStart"]
        //放款时间结束
        def startDateEnd = params["startDateEnd"]
        //面积上限
        def buildAreaStart = params["buildAreaStart"]
        //面积下限
        def buildAreaEnd = params["buildAreaEnd"]
        //贷款到期日开始
        def endDateStart = params["endDateStart"]
        //贷款到期日结束
        def endDateEnd = params["endDateEnd"]
        //贷款时长
        def loansDuration = params["loansDuration"]
        //风险
        def risk = params["risk"]
        def sql = ""
        //查询条件
        if (city && city != '-1')
        {
            sql += " AND A.ZONE_NUMBER = '${city}'"
        }
        if (district && district != '-1')
        {
            sql += " AND B.ZONE_NUMBER = '${district}'"
        }
        if (loansBalanceStart && loansBalanceStart != '')
        {
            sql += " AND E.LOANS_BALANCE >= ${loansBalanceStart}*10000"
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            sql += " AND E.LOANS_BALANCE <= ${loansBalanceEnd}*10000"
        }
        if (loansAmountStart && loansAmountStart != '')
        {
            sql += " AND E.LOANS_AMOUNT >= ${loansAmountStart}*10000"
        }
        if (loansAmountEnd && loansAmountEnd != '')
        {
            sql += " AND E.LOANS_AMOUNT <= ${loansAmountEnd}*10000"
        }
        if (startDateStart && startDateStart != '')
        {
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (startDateEnd && startDateEnd != '')
        {
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        if (buildAreaStart && buildAreaStart != '')
        {
            sql += " AND E.SOURCE_BUILD_AREA >= '${buildAreaStart}'"
        }
        if (buildAreaEnd && buildAreaEnd != '')
        {
            sql += " AND E.SOURCE_BUILD_AREA <= '${buildAreaEnd}'"
        }
        if (endDateStart && endDateStart != '')
        {
            def dates = endDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " AND TO_CHAR(E.END_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (endDateEnd && endDateEnd != '')
        {
            def datee = endDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " AND TO_CHAR(E.END_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    def minValue = it.minValue
                    def maxValue = it.maxValue
                    sql += " AND E.LOANS_DURATION > '${minValue}' AND E.LOANS_DURATION <= '${maxValue}'"
                }
            }
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    sql += " AND E.CLTV > '${minValue}' AND E.CLTV <= '${maxValue}'"
                }

            }
        }
        return sql
    }

    /******************增值空间 （机构过滤）**************************/
    def valueAddedSpace()
    {
        respond(view: "valueAddedSpace")
    }

    def valueAddedSpaceData()
    {
        def user = User.findById(springSecurityService.principal.id)
        def db = new groovy.sql.Sql(dataSource)
        def result = new ArrayList()
        def status = ""
        try
        {
            def sql = " SELECT VRD.ID,VRD.Label,Count(DS.Add_Rate) AS Amount,VRD.Order_Number FROM " + "(SELECT * FROM DIMENSION where Class = 'com.cindata.dimension.ValueRateDimension') VRD LEFT JOIN " + "( SELECT (E.Total_Price - E.Loans_Evaluate_Total_Price)/E.Loans_Evaluate_Total_Price*100 AS Add_Rate" + " from ESTATE E " + "left join area a on e.city_id = a.area_id " + "left join area b on e.district_id = b.area_id " + "WHERE E.Total_Price IS NOT null AND E.Total_Price  <> 0 and E.Loans_Evaluate_Total_Price IS NOT null AND E.Loans_Evaluate_Total_Price IS NOT NULL "
            def accountSql = Account.FilterAccountSql(user)
            if (accountSql)
            {
                sql += " AND E.ORG_ID IN ${accountSql}"
            }
            sql += pubSql(params)
            sql += " ) DS ON DS.Add_Rate BETWEEN VRD.Min_Value AND Max_Value "
            sql += " GROUP BY VRD.ID,VRD.Label,VRD.Order_Number ORDER BY VRD.Order_Number"
            result = db.rows(sql).asList()
            status = "success"
        }
        catch (Exception e)
        {
            e.printStackTrace()
            status = "false"
        }
        render([result: result, status: status] as JSON)
    }

    /******************增值分布 （机构过滤）**************************/
    def valueAddedDistributed()
    {
        respond(view: "valueAddedDistributed")
    }

    def valueAddedDistributedData()
    {
        def user = User.findById(springSecurityService.principal.id)
        def topNum = params["topNum"]
        def db = new groovy.sql.Sql(dataSource)
        def result = new ArrayList()
        def status = ""
        try
        {
            if (!topNum)
            {
                topNum = 10
            }
            else
            {
                topNum = topNum.toInteger()
            }
            def sql = "SELECT DA.* FROM (SELECT E.COMMUNITY_ID,E.COMMUNITY_NAME,COUNT(E.COMMUNITY_ID) AS Amount, " + " (SUM(E.Total_Price) - SUM(E.Loans_Evaluate_Total_Price))/SUM(E.Loans_Evaluate_Total_Price)*100 AS Add_Rate" + " from ESTATE E" + " left join area a on e.city_id = a.area_id " + " left join area b on e.district_id = b.area_id " + " WHERE E.Total_Price IS NOT null and E.Loans_Evaluate_Total_Price IS NOT null "
            def accountSql = Account.FilterAccountSql(user)
            if (accountSql)
            {
                sql += " AND E.ORG_ID IN ${accountSql} "
            }
            sql += pubSql(params)
            sql += " GROUP BY E.COMMUNITY_ID,E.COMMUNITY_NAME) DA WHERE 1=1 ORDER BY DA.Add_Rate DESC "
            result = db.rows(sql, 0, topNum).asList()
            status = "success"
        }
        catch (Exception e)
        {
            e.printStackTrace()
            status = "false"
        }
        render([result: result, status: status] as JSON)
    }

    /******************下跌分布 （机构过滤）**************************/
    def valueFallDistributed()
    {
        respond(view: "valueFallDistributed")
    }

    def valueFallDistributedData()
    {
        def user = User.findById(springSecurityService.principal.id)
        def topNum = params["topNum"]
        def db = new groovy.sql.Sql(dataSource)
        def result = new ArrayList()
        def status = ""
        try
        {
            if (!topNum)
            {
                topNum = 10
            }
            else
            {
                topNum = topNum.toInteger()
            }
            def sql = "SELECT DA.* FROM (SELECT E.COMMUNITY_ID,E.COMMUNITY_NAME,COUNT(E.COMMUNITY_ID) AS Amount, " + " (SUM(E.Loans_Evaluate_Total_Price) - SUM(E.Total_Price))/SUM(E.Total_Price)*100 AS Add_Rate" + " from ESTATE E" + " left join area a on e.city_id = a.area_id " + " left join area b on e.district_id = b.area_id " + " WHERE E.Total_Price IS NOT null and E.Loans_Evaluate_Total_Price IS NOT null "
            def accountSql = Account.FilterAccountSql(user)
            if (accountSql)
            {
                sql += " AND E.ORG_ID IN ${accountSql} "
            }
            sql += pubSql(params)
            sql += " GROUP BY E.COMMUNITY_ID,E.COMMUNITY_NAME) DA WHERE 1=1 ORDER BY DA.Add_Rate DESC "
            result = db.rows(sql, 0, topNum).asList()
            status = "success"
        }
        catch (Exception e)
        {
            e.printStackTrace()
            status = "false"
        }
        render([result: result, status: status] as JSON)
    }
}
